% cohort method to statistics on duration-year data
clear all;
clc;
% freqj=4;% 4 quarters
year_list=1998:2010;

DEFAULT = -2;
DURATION = 1;
NUMBER_OF_YEAR = length(year_list) - DURATION;

year_list_text=num2str(year_list);
% quarter_list=[1:4]';
% quarter_list_text=num2str(quarter_list);
rateList= 1.5:0.5:4.5;
rateList=[rateList, DEFAULT];
RATE_LIST_LENGTH = length(rateList);
db1_open = database('db2_fin','', ''); %open the connection to the database
%table = 'TP06_Y_RJ_fin_R5_G1000k_MR7';
%table = 'TP06_Y_RJ_fin_R5_G250_1000k_MR7';
%table ='TP06_Y_RJ_fin_R5_G50_250_MR7';
table ='TP06_Y_RJ_fin_R5_G50_MR7';

durationCount = zeros(NUMBER_OF_YEAR,RATE_LIST_LENGTH -1,RATE_LIST_LENGTH);

for yearIdx=1:NUMBER_OF_YEAR % i=1:11 number of year
    
    year1 = num2str(year_list(yearIdx));
    year2 = num2str(year_list(yearIdx+DURATION));
    year_lookfor=year_list(yearIdx:yearIdx+DURATION);
    year_lookfor_length = length(year_lookfor);
    
    
    %get data for 3 years
    param = ['SELECT Year, [Customer Number] as custumerId, [Overall Risk Rating (Hist)] as riskRating, status, default FROM ' table ' where year BETWEEN ''' year1 ''' and ''' year2 ''''];
    sqlquery1 = strcat(param);
   
    setdbprefs('DataReturnFormat','structure')
    results = fetch(db1_open, sqlquery1);
    
    val = size(results.Year);
    
    custumersMap = containers.Map();
    
    % ---------------------------------------------------------------------
    % FILL RATE
    
    for i =1:val
        
        custumerIdStr = results.custumerId{i};
        riskRating = results.riskRating(i);
        year = str2double(results.Year(i));
        
        
        default = strcmp('Default', results.default(i));
        unhealthy = strcmp('Unhealthy', results.status(i));
        
        if isKey(custumersMap, custumerIdStr) == 0
            %the custumer is not in the map, so create it
            
            %create structure
            myStruct.custumerId = custumerIdStr;
            myStruct.rates = -1 * ones (1, year_lookfor_length); %[-1, -1, -1];
            
        else
            myStruct = custumersMap(custumerIdStr);
        end
        % put the right rating
        myStruct = fillRate(myStruct, year_lookfor, year, riskRating, default, unhealthy);
        
        % insert data in the map
        custumersMap(custumerIdStr) = myStruct;
    end
    
   
    
    

    
    % ---------------------------------------------------------------------
    % FILTRE
    % -1 == disapear or don't exist
    % -2 == default
    % 0  == i don't know
    
    custumersStructures = custumersMap.values;
    custumersStructuresLength = length(custumersStructures);
    
    
    
    for i = 1:custumersStructuresLength
        myStruct = custumersStructures{i};

        rate = myStruct.rates(1);
        if rate == -1 || rate == 0
            remove(custumersMap, myStruct.custumerId);
        else
            for j = 2 : year_lookfor_length
                 rate = myStruct.rates(j);
                 if rate == DEFAULT
                     myStruct.rates(year_lookfor_length) = DEFAULT;
                 end
            end
        end
    end
    
    
    % ---------------------------------------------------------------------
    % COUNT

    custumersStructures = custumersMap.values;
    custumersStructuresLength = length(custumersStructures);
    
    year_lookfor_length = length(year_lookfor);
    
    
    
    
    for i = 1 : custumersStructuresLength
        myStruct = custumersStructures{i};
        
        rate = myStruct.rates(1);
        position1 = find(rateList == rate);
        
        rate = myStruct.rates(year_lookfor_length);
        position2 = find(rateList == rate);
        
        
        %get the count for one path
        pCount = durationCount(yearIdx,position1,position2);
        
        %increment the count + 1 for that path
        durationCount(yearIdx,position1,position2) = pCount + 1;
    end
    
    disp( strcat(['Year: ' year1 ' to ' year2]));

end
close (db1_open);

durationProb=zeros(1,RATE_LIST_LENGTH -1,RATE_LIST_LENGTH);

for y=1:NUMBER_OF_YEAR
    s = sum(durationCount(y, :, :),3);
    for i=1:RATE_LIST_LENGTH
        durationProb(y, :, i)= durationCount(y, :, i)./s;
    end
end

%------------------Edit on 2011 Nov 18------------------------------------
%To calculate the average counts and prob

% PathCountTotal=sum(durationCount(1:NUMBER_OF_YEAR,:,:));
% PathCountAvg=PathCountTotal./NUMBER_OF_YEAR;







